﻿create PROCEDURE Sync.SP_CommitteeBankAccount_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccount_Temp TABLE 
		(
			CommitteeBankAccountID UniqueIdentifier,
			[CommitteeID] uniqueidentifier,
			[BankAccountNumber] nvarchar(20),
			[ProjectID] uniqueidentifier,
			[Balance] float,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccount_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountID[1]','uniqueidentifier') CommitteeBankAccountID,
                Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
				Tbl.Col.value('BankAccountNumber[1]', 'nvarchar(20)') [BankAccountNumber],
				Tbl.Col.value('ProjectID[1]', 'uniqueidentifier') [ProjectID],
				Tbl.Col.value('Balance[1]','float') [Balance],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccount') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccount] ([CommitteeBankAccountID],[CommitteeID],[BankAccountNumber],[ProjectID],[Balance])
	SELECT t.[CommitteeBankAccountID],t.[CommitteeID],t.[BankAccountNumber],t.[ProjectID],[Balance]
	FROM  @CommitteeBankAccount_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccount_Tracking s
        WHERE t.CommitteeBankAccountID = s.CommitteeBankAccountID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccount]
	SET [CommitteeBankAccount].[CommitteeID] = t.[CommitteeID],[CommitteeBankAccount].[BankAccountNumber] = t.[BankAccountNumber],[CommitteeBankAccount].[ProjectID] = t.[ProjectID],[CommitteeBankAccount].[Balance] = t.[Balance]
	FROM [dbo].[CommitteeBankAccount] s JOIN @CommitteeBankAccount_Temp t 
    ON t.CommitteeBankAccountID = s.CommitteeBankAccountID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccount_Tracking r
				  WHERE t.CommitteeBankAccountID = r.CommitteeBankAccountID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccount]
    FROM @CommitteeBankAccount_Temp t JOIN [dbo].[CommitteeBankAccount] s
    ON t.CommitteeBankAccountID = s.CommitteeBankAccountID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











